package backpage.model;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import com.mysql.jdbc.Connection;
//import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import backpage.view.PageDivision;
import backpage.view.ResItem;

public class DBAccessor {
	private String dbDriverStr = "org.gjt.mm.mysql.Driver";
	//private String dbDriverStr = "com.mysql.jdbc.Driver";
	private String dbConnStr = "jdbc:mysql://localhost:3306/se_site?user=root&password=111111&characterEncoding=UTF-8" ;
	private Connection connection;
	private Statement statement;
	public static final DBAccessor Instance = new DBAccessor();
	
	private DBAccessor(){
		try{
			Class.forName(dbDriverStr);		//create DataBase Driver
		}catch(ClassNotFoundException e){
			System.err.println("DBDriver class not found");
		}
		try {
			connection = (Connection)DriverManager.getConnection(dbConnStr);
			statement = (Statement)connection.createStatement();
		} catch (SQLException e) {
			System.err.println(e);
		}
	}
	
	public PageDivision getPageDivision(String resType, String sortKey, String upOrDown ,
			String pageCount, /*number of items in one page*/
			String pageIndex ){	  /*index of the page required*/
		PageDivision pageDivision = new PageDivision(0, 0, new ArrayList<ResItem>());
		
		try {
			ResultSet rs = (ResultSet) statement.executeQuery(	//get the query result
					"select		* " +
					"from  		resources " +
					"where 		res_type = '" + resType + "' " +
					"order by	'" + sortKey + "' " + upOrDown );
			
			ArrayList itemList = new ArrayList();
			
			while( rs.next() ){	//extract all query result to a list 
				ResItem resItem = new ResItem();
				resItem.setID(rs.getInt("ID"));
				resItem.setPath(rs.getString("path"));
				resItem.setFileName(rs.getString("file_name"));
				resItem.setSubmitTime(rs.getDate("create_time"));
				resItem.setDownloadNum(Integer.parseInt(rs.getString("download_num")));
				resItem.setTouchNum(Integer.parseInt(rs.getString("touch_num")));
				resItem.setDescription(rs.getString("description"));
				itemList.add(resItem);
				//System.out.println(resItem.getFileName()+"  "+resItem.getPath()+"  "+resItem.getDownloadNum()+"  "+resItem.getTouchNum()+"  "+resItem.getSubmitTime());
			}
			
			//set the totalPage
			int totalPage = itemList.size() / Integer.parseInt(pageCount);
			if( itemList.size() % Integer.parseInt(pageCount) != 0 )
				totalPage ++;
			pageDivision.setTotalPageNum(totalPage);

			//set the curPage
			if( Integer.parseInt(pageIndex) > totalPage )
				pageIndex = Integer.toString(totalPage);			
			pageDivision.setCurPage( Integer.parseInt(pageIndex) );
			if( totalPage == 0 )
				pageDivision.setCurPage(0);
			
			//get the sublist for the page required
			int start = Integer.parseInt(pageCount) * ( Integer.parseInt(pageIndex) - 1 );
			if( start < 0 ) start = 0;
			int end = start + Integer.parseInt(pageCount);
			if( end > itemList.size() )
				end = itemList.size();
			if( start > end - 1 && end > 0 )
				start = end - 1;
			
			pageDivision.setitemList( new ArrayList( itemList.subList(start, end)) );
						
		} catch (SQLException e) {
			System.err.println(e);
			return pageDivision;
		}
		
		return pageDivision;
	}
		
	public void deleteResByID(int id){
		try {
			statement.executeUpdate( "delete from resources where id = " + id );
		} catch (SQLException e) {
			System.err.println(e);
		}
	}
	
	public String getResFullPathByID(int id){
		try{
			ResultSet rs = (ResultSet)statement.executeQuery( "select path, file_name from resources where id = " + id );
			if( rs.next() )
				return rs.getString("path") + rs.getString("file_name");
		}catch(SQLException e){
			System.out.println(e);
			return null;
		}
		return null;
	}
	
	public void insertRes( String resType, String dir, String fileName, String description){
		Date date = new Date(System.currentTimeMillis());
		dir = dir.replace('\\', '/');	//replace all \ to /, it's important
		try{
			ResultSet rs = (ResultSet)statement.executeQuery(	//get the ID currently biggest
					"select ID " +
					"from	resources " +
					"where	ID >= all ( select ID from resources )");
			int id;
			if(rs.next())
				id = rs.getInt("ID") + 1;
			else id = 0;
			statement.executeUpdate(
				"insert into resources values (" +
				"'" + id + "'," +
				"'" + dir + "'," +
				"'" + date + "'," +
				"'0'," +
				"'0'," +
				"'" + resType + "'," +
				"'" + fileName + "',"+
				"'"+description+"')");
		}catch(SQLException e){
			System.err.println(e);
		}
	}
	
	public void incDownNum(String ID){	//increase the download_num of the specified resource
		try {
			statement.executeUpdate(
					"update resources " +
					"set	download_num = download_num + 1 " +
					"where	ID = " +
					ID);
		} catch (SQLException e) {
			System.out.println(e);
		}
	}
	
	public void incTouchNum(String ID){	//increase the touch_num of the specified resource
		try {
			statement.executeUpdate(
					"update resources " +
					"set	touch_num = touch_num + 1 " +
					"where	ID = " +
					ID);
		} catch (SQLException e) {
			System.err.println(e);
		}		
	}
	
	public boolean resExists(String fileName){
		try{
			ResultSet rs = (ResultSet)statement.executeQuery( "select file_name from resources where file_name = '" + fileName + "'" );
			if( rs.next() )
				return true;
		}catch(SQLException e){
			System.out.println(e);
			return false;
		}
		return false;
	}
	
	public PageDivision doSearch(String resType, String sortKey, String upOrDown , String keyWord, String keyType,
			String pageCount, /*number of items in one page*/
			String pageIndex ){	  /*index of the page required*/
		PageDivision pageDivision = new PageDivision(0, 0, new ArrayList<ResItem>());
		
		String querySentence = "select * from resources where ";
		if( keyType.equals("all") )
			querySentence += "( file_name like '%" + keyWord + "%' " +
				" or description like '%" + keyWord + "%' " +
				" or create_time like '%" + keyWord + "%' ) ";
		else querySentence += keyType + " like '%" + keyWord + "%' ";
		if( !resType.equals("all") )
			querySentence += " and res_type = '" + resType + "' ";
		querySentence += " order by '" + sortKey + "' " + upOrDown;
		System.out.println(querySentence);
		try {
			ResultSet rs = (ResultSet) statement.executeQuery(querySentence);	//get the query result
								
			ArrayList itemList = new ArrayList();
			
			while( rs.next() ){	//extract all query result to a list 
				ResItem resItem = new ResItem();
				resItem.setID(rs.getInt("ID"));
				resItem.setPath(rs.getString("path"));
				resItem.setFileName(rs.getString("file_name"));
				resItem.setSubmitTime(rs.getDate("create_time"));
				resItem.setDownloadNum(Integer.parseInt(rs.getString("download_num")));
				resItem.setTouchNum(Integer.parseInt(rs.getString("touch_num")));
				resItem.setDescription(rs.getString("description"));
				itemList.add(resItem);
				//System.out.println(resItem.getFileName()+"  "+resItem.getPath()+"  "+resItem.getDownloadNum()+"  "+resItem.getTouchNum()+"  "+resItem.getSubmitTime());
			}
			
			//set the totalPage
			int totalPage = itemList.size() / Integer.parseInt(pageCount);
			if( itemList.size() % Integer.parseInt(pageCount) != 0 )
				totalPage ++;
			pageDivision.setTotalPageNum(totalPage);

			//set the curPage
			if( Integer.parseInt(pageIndex) > totalPage )
				pageIndex = Integer.toString(totalPage);			
			pageDivision.setCurPage( Integer.parseInt(pageIndex) );
			if( totalPage == 0 )
				pageDivision.setCurPage(0);
			
			//get the sublist for the page required
			int start = Integer.parseInt(pageCount) * ( Integer.parseInt(pageIndex) - 1 );
			if( start < 0 ) start = 0;
			int end = start + Integer.parseInt(pageCount);
			if( end > itemList.size() )
				end = itemList.size();
			if( start > end - 1 && end > 0 )
				start = end - 1;
			
			pageDivision.setitemList( new ArrayList( itemList.subList(start, end)) );
						
		} catch (SQLException e) {
			System.err.println(e);
			return pageDivision;
		}
		
		return pageDivision;
	}

}